<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1" import="java.sql.*,support.*, myclasses.*, java.util.*"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Applications</title>
<script type="text/javascript" src="jquery-1.7.2.js"></script>
<script type="text/javascript">
function showData(id) {
	var URL = "http://localhost:8080/GradSchoolApp/getInfo.jsp?id=" + id;

	var ret = $.getJSON(URL, "", function(data, textStatus) {
		document.getElementById('hideBtn_' + id).style.display = '';
		document.getElementById('showBtn_' + id).style.display = 'none';
		document.getElementById('degH').style.display = '';
		document.getElementById('cityH').style.display = '';
		document.getElementById('resH').style.display = '';
		document.getElementById('specH').style.display = '';
		document.getElementById('city_' + id).style.display = '';
		document.getElementById('residence_' + id).style.display = '';
		document.getElementById('spec_' + id).style.display = '';
		document.getElementById('deg_' + id).style.display = '';
		for (var i = 0; i < data.length; i++) {
			document.getElementById('major_' + data[i].degreeId).value = data[i].major;
			document.getElementById('title_' + data[i].degreeId).value = data[i].title;
			document.getElementById('university_' + data[i].degreeId).value = data[i].university;
			document.getElementById('gpa_' + data[i].degreeId).value = data[i].gpa;
			document.getElementById('year_' + data[i].degreeId).value = data[i].year;
			document.getElementById('month_' + data[i].degreeId).value = data[i].month;
		}
	},"json");
	//.error(function(jqXHR, textStatus, errorThrown) { alert("error: " + textStatus + " : " + errorThrown); });
}
function hideInfo(id) {
	document.getElementById('city_' + id).style.display = 'none';
	document.getElementById('residence_' + id).style.display = 'none';
	document.getElementById('spec_' + id).style.display = 'none';
	document.getElementById('cityH').style.display = 'none';
	document.getElementById('resH').style.display = 'none';
	document.getElementById('specH').style.display = 'none';
	document.getElementById('deg_' + id).style.display = 'none';
	document.getElementById('degH').style.display = 'none';
	document.getElementById('showBtn_' + id).style.display = '';
	document.getElementById('hideBtn_' + id).style.display = 'none';
}
</script>
</head>
<body>
<%	
	String spec = request.getParameter("specName");
	String disc = request.getParameter("discName");
%>
<%
	if(spec != null) {
		%> 
		<center><h1><%=spec %></h1></center>
		<%
		try {
			Class.forName("org.postgresql.Driver");
		} catch(Exception e) {
			out.println("org.postgresql.Driver Exception. ");
		}
		try {
			String serverName = "localhost:5432";
		    String mydatabase = "ThirdBase";
		    String url = "jdbc:postgresql://" + serverName + "/" + mydatabase; 
		
		    String username = "SweetAss";
		    String password = "1234";
		    Connection conn = DriverManager.getConnection(url, username, password);
			
		    String query = 
		    		"SELECT firstname,middleinitial,lastname, address.city, country.name " +
		    		"FROM 	degree JOIN applicant ON degree.applicant = applicant.id " +
		    			"JOIN specialization ON applicant.specialization = specialization.id " +
		    			"JOIN address ON applicant.address = address.id " +
		    			"JOIN country ON address.residence = country.id " +
		    		"WHERE specialization.name = '" + spec + "'" +
		    		"GROUP BY applicant.id, address.city, country.name " +
		    		"ORDER BY applicant.firstname";
		    
			Statement stmt = conn.createStatement();
			ResultSet rset = stmt.executeQuery(query);
			
			out.println("<table width=\"600\" border=\"2\" cellpadding=\"2\" cellspacing=\"2\" align=\"center\">");
			out.println("<tr>");
			out.println("<td><B>First name</B></td>");
			out.println("<td><B>Middle initial</B></td>");
			out.println("<td><B>Last name</B></td>");
			out.println("<td><B>City</B></td>");
			out.println("<td><B>Country of residence</B></td>");
			out.println("</tr>");
			//print out specializations
			while(rset.next()) {
				out.println("<tr>");
				out.println("<td>" + rset.getString(1) + "</td>");
				out.println("<td>" + rset.getString(2) + "</td>");
				out.println("<td>" + rset.getString(3) + "</td>");
				out.println("<td>" + rset.getString(4) + "</td>");
				out.println("<td>" + rset.getString(5) + "</td>");
				out.println("</tr>");
			}
		
			out.println("</table>");
			out.println("<br>");
			
			//close connections
			rset.close();
			stmt.close();
			conn.close();
		
		} catch(Exception e) {
			out.println("Connection exception.");
		}
	}
	else if (disc != null) {
		%> 
		<center><h1><%=disc %></h1></center>
		<%
		try {
			Class.forName("org.postgresql.Driver");
		} catch(Exception e) {
			out.println("org.postgresql.Driver Exception. ");
		}
		try {
			String serverName = "localhost:5432";
		    String mydatabase = "ThirdBase";
		    String url = "jdbc:postgresql://" + serverName + "/" + mydatabase; 
		
		    String username = "SweetAss";
		    String password = "1234";
		    Connection conn = DriverManager.getConnection(url, username, password);
			
		    String query = 
		    		"SELECT firstname,middleinitial,lastname, address.city, country.name,specialization.name " +
		    		"FROM 	degree JOIN applicant ON degree.applicant = applicant.id " +
		    			"JOIN major ON degree.major = major.id " +
		    			"JOIN specialization ON applicant.specialization = specialization.id " +
		    			"JOIN address ON applicant.address = address.id " +
		    			"JOIN country ON address.residence = country.id " +
		    		"WHERE major.name = '" + disc + "'" + 
		    		"GROUP BY applicant.id, specialization.name, address.city, country.name " +
		    		"ORDER BY applicant.firstname";
		    
			Statement stmt = conn.createStatement();
			ResultSet rset = stmt.executeQuery(query);
			
			out.println("<table width=\"600\" border=\"2\" cellpadding=\"2\" cellspacing=\"2\" align=\"center\">");
			out.println("<tr>");
			out.println("<td><B>First name</B></td>");
			out.println("<td><B>Middle initial</B></td>");
			out.println("<td><B>Last name</B></td>");
			out.println("<td><B>City</B></td>");
			out.println("<td><B>Country of residence</B></td>");
			out.println("<td><B>Specialization</B></td>");
			out.println("</tr>");
			//print out specializations
			while(rset.next()) {
				out.println("<tr>");
				out.println("<td>" + rset.getString(1) + "</td>");
				out.println("<td>" + rset.getString(2) + "</td>");
				out.println("<td>" + rset.getString(3) + "</td>");
				out.println("<td>" + rset.getString(4) + "</td>");
				out.println("<td>" + rset.getString(5) + "</td>");
				out.println("<td>" + rset.getString(6) + "</td>");
				out.println("</tr>");
			}
		
			out.println("</table>");
			out.println("<br>");
			
			//close connections
			rset.close();
			stmt.close();
			conn.close();
		
		} catch(Exception e) {
			out.println("Connection exception.");
		}
	}
	else if (spec == null && disc == null) {
		%> 
		<center><h1>Applications</h1></center>
		<%
			try {
			Class.forName("org.postgresql.Driver");
				} catch(Exception e) {
			out.println("org.postgresql.Driver Exception. ");
				}
				try {
			String serverName = "localhost:5432";
		    String mydatabase = "ThirdBase";
		    String url = "jdbc:postgresql://" + serverName + "/" + mydatabase; 
		
		    String username = "SweetAss";
		    String password = "1234";
		    Connection conn = DriverManager.getConnection(url, username, password);
	
		    String query = 
		    		"SELECT firstname,middleinitial,lastname, address.city, country.id, country.name, specialization.name, applicant.id " +
		    		"FROM 	degree JOIN applicant ON degree.applicant = applicant.id " +
		    			"JOIN specialization ON applicant.specialization = specialization.id " +
		    			"JOIN address ON applicant.address = address.id " +
		    			"JOIN country ON address.residence = country.id " +
		    		"GROUP BY applicant.id, specialization.name, address.city, country.id, country.name " +
		    		"ORDER BY applicant.firstname;";

			Statement stmt = conn.createStatement();
			ResultSet rset = stmt.executeQuery(query);

			ArrayList<Student> studArr = new ArrayList<Student>();

			while(rset.next())
			{
				Student stud = new Student(rset.getString(1),rset.getString(2),rset.getString(3));
				
				Address add = new Address("",rset.getString(4),"","","","","");
				stud.setAddress(add);
				
				stud.setResidenceId(rset.getString(5));
				stud.setResidenceName(rset.getString(6));
				stud.setSpecialization(rset.getString(7));
				stud.setId(rset.getString(8));
				studArr.add(stud);
			}

			String degQuery = "SELECT major.name, degree.id, applicant " +
							"FROM degree JOIN major ON degree.major = major.id";

			Statement degStmt = conn.createStatement();
			ResultSet degRset = degStmt.executeQuery(degQuery);
			
			while(degRset.next())
			{
				Degree deg = new Degree();
				deg.setName(degRset.getString(1));
				deg.setId(degRset.getString(2));

				for(int i = 0;i<studArr.size();i++){
					if(Integer.parseInt(studArr.get(i).getId()) == degRset.getInt(3)) {
						studArr.get(i).addDegree(deg);
					}
				}
			}
			%>
			<table width="900" border="2" cellpadding="2" cellspacing="2" align="center">
			<tr>
				<td><B>First name</B></td>
				<td><B>Middle initial</B></td>
				<td><B>Last name</B></td>
				<td style="display:none;" id="cityH"><B>City</B></td>
				<td style="display:none;" id="resH"><B>Country of residence</B></td>
				<td style="display:none;" id="specH"><B>Specialization</B></td>
				<td style="display:none;" id="degH"><B>Degree(s)</B></td>
			</tr>
			
			<%
			for(int i = 0; i<studArr.size();i++) {
			%>
				<tr id="<%=i%>">
				<td> <%=studArr.get(i).getFirstName() %></td>
				<td> <%=studArr.get(i).getMiddleInitial() %></td>
				<td> <%=studArr.get(i).getLastName() %></td>
				
				<td style="display:none;" id="city_<%=studArr.get(i).getId()%>"> <%=studArr.get(i).getAddress().getCity() %></td>
				<td style="display:none;" id="residence_<%=studArr.get(i).getId()%>"> <%=studArr.get(i).getResidenceName()%></td>
				<td style="display:none;" id="spec_<%=studArr.get(i).getId()%>"> <%=studArr.get(i).getSpecialization() %></td>
				<td style="display:none;" id="deg_<%=studArr.get(i).getId()%>">
					<%
					for(Degree d: studArr.get(i).getDegrees())
					{
						%><p>Major: <input id="major_<%=d.getId() %>" value="" readonly="readonly" style="border: none" size="35" ><br>
						Title:      <input id="title_<%=d.getId() %>" value="" readonly="readonly" style="border: none" size="20" ><br>
						University: <input id="university_<%=d.getId() %>" value="" readonly="readonly" style="border: none" size="30" ><br>
						GPA:        <input id="gpa_<%=d.getId() %>" value="" readonly="readonly" style="border: none" size="20" ><br>
						Year:       <input id="year_<%=d.getId() %>" value="" readonly="readonly" style="border: none" size="20" ><br>
						Month:      <input id="month_<%=d.getId() %>" value="" readonly="readonly" style="border: none" size="20" ><br></p><%
					}
					%>
				</td>
				<td>
					<input id="showBtn_<%=studArr.get(i).getId() %>" onClick="showData(<%=studArr.get(i).getId() %>);" type="button" value="Show"/>
					<input id="hideBtn_<%=studArr.get(i).getId() %>" style="display:none;" onClick="hideInfo(<%=studArr.get(i).getId() %>);" type="button" value="Hide"/>
				</td>
				<%
			}
			%>
			</table>
			<br>	
			<%
			
			//close connections
			rset.close();
			stmt.close();

			degRset.close();
			degStmt.close();
			conn.close();
		
		} catch(Exception e) {
			out.println(e.getMessage());
			out.println("Connection exception.");
		}
	}
%>

</body>
</html>